﻿using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Cache;
using com.nm.Helper;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using Nancy.Extensions;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using VerticaAPI.model;

namespace VerticaAPI
{
    [Description("智能补货结果")]
    public class DWSuppleAllMessageHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 没SQL不用改版

        #region 智能补货结果列表 GetDWSuppleAllMessageList

        [MustLogin]
        [Description("智能补货结果列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, DWSuppleAllMessageModel.NeceModel>>))]
        public ApiResult GetDWSuppleAllMessageList([ParamDescription("发货仓库")] string FHStockID, [ParamDescription("省份")] string ProID = "",
           [ParamDescription("城市")] string CityID = "", [ParamDescription("店铺Keys")] string ShopKeys = "", [ParamDescription("货品类别")] string KindName = "",
           [ParamDescription("货品年份")] string YearNo = "", [ParamDescription("货品季节")] string SeasonName = "", [ParamDescription("货号Keys")] string MaterialID = "",
           [ParamDescription("货品款型")] string ModelName = "", [ParamDescription("补货筛选")] long? SuppleQty = 1L, [ParamDescription("店铺级别")] string ShopLevelCode = "",
            //添加  Project   Gender  ChildProject
            [ParamDescription("货品项目")] string Project = "", [ParamDescription("性别")] string Gender = "", [ParamDescription("子项目")] string ChildProject = "",
           [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                if (string.IsNullOrWhiteSpace(FHStockID)) return Error("发货仓库不能为空！");
                var count = ReturnTotalDWSuppleAllMessage(FHStockID, ProID, CityID, ShopKeys, KindName,
                    YearNo, SeasonName, MaterialID, ModelName, SuppleQty, ShopLevelCode);
                var result = PaperData<IGrouping<string, DWSuppleAllMessageModel.NeceModel>>.Create(count, Size, Page);

                var dt = ReturnDWSuppleAllMessageList(FHStockID, ProID, CityID, ShopKeys, KindName,
                    YearNo, SeasonName, MaterialID, ModelName, SuppleQty, ShopLevelCode, Project, Gender, ChildProject);

                var list = ModelConvertHelper<DWSuppleAllMessageModel.NeceModel>.ConvertToModel(dt).ToList();
                if (SuppleQty != -1 && list.Count > 0)
                {
                    var shopCodeList = list.Select(d => d.ShopCode).Distinct().ToList().Select(item => "'" + item + "'").ToList();
                    var materialIDList = list.Select(d => d.MaterialID).Distinct().ToList().Select(item => "'" + item + "'").ToList();
                    var inventoryDT = ReturnTotalInventory(FHStockID, string.Join(",", materialIDList), string.Join(",", shopCodeList), SuppleQty);
                    var inventoryList = ModelConvertHelper<DWSuppleAllMessageModel.NeceModel>.ConvertToModel(inventoryDT).ToList();
                    list.AddRange(inventoryList);
                }
                var a = list.GroupBy(d => d.MaterialID).OrderByDescending(d => d.Sum(t => t.AvgWeekSaleQty));
                var newGroup = new List<IGrouping<string, DWSuppleAllMessageModel.NeceModel>>();
                foreach (var item in a)
                {
                    var b = item.GroupBy(d => d.FHStockID + "_" + d.MaterialID + "_" + d.ShopCode)
                            .Where(d => d.First().FHStockName != null).OrderByDescending(t => t.Sum(d => d.RobSuppleQty))
                            .Select(d => d)
                            .ToList();
                    newGroup.AddRange(b);
                }
                result.Items = newGroup.Skip((Page - 1) * Size).Take(Size).ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
        #endregion

        #region 辅助信息 GetDWAuxiliaryInformationList

        [MustLogin]
        [Description("辅助信息")]
        [ReturnType(typeof(IList<DWSuppleAllMessageModel.FinalModel>))]
        public ApiResult GetDWAuxiliaryInformationList([ParamDescription("发货仓库")] string FHStockID, [ParamDescription("店铺Keys")] string ShopCode, [ParamDescription("货号Keys")] string MaterialID)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                if (string.IsNullOrWhiteSpace(FHStockID)) return Error("发货仓库不能为空！");
                if (string.IsNullOrWhiteSpace(ShopCode)) return Error("请选择店铺！");
                if (string.IsNullOrWhiteSpace(MaterialID)) return Error("货号不能为空！");
                var dt = ReturnDWAuxiliaryInformationList(FHStockID, ShopCode, MaterialID);
                var result = ModelConvertHelper<DWSuppleAllMessageModel.FinalModel>.ConvertToModel(dt).ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region 库存列表 GetDWFHStockCanUseList

        [MustLogin]
        [Description("库存列表")]
        [ReturnType(typeof(List<IGrouping<string, DWFHStockCanUseModel.ListModel>>))]
        public ApiResult GetDWFHStockCanUseList([ParamDescription("发货仓库")]string FHStockID, [ParamDescription("货号ID")] string MaterialID)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }

            try
            {
                if (string.IsNullOrEmpty(FHStockID)) return Error("发货仓库不能为空！");
                if (string.IsNullOrEmpty(MaterialID)) return Error("货号不能为空！");
                var dt = ReturnDWFHStockCanUseList(FHStockID, MaterialID);
                var result =
                    ModelConvertHelper<DWFHStockCanUseModel.ListModel>.ConvertToModel(dt).OrderByDescending(d => d.Stockdate)
                    .DistinctBy(d => new { d.FHStockID, d.MaterialID, d.SizeID })
                        .GroupBy(d => d.FHStockID + "_" + d.MaterialID + "_" + d.Stockdate)
                        .ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
        #endregion

        #region 修改智能补货结果列表 ModifyDWSuppleAllMessageList

        [JsonType]
        [MustLogin]
        [Description("修改智能补货结果列表")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModifyDWSuppleAllMessageList(
     [ParamDescription("修改列表")] DWSuppleAllMessageModel.ModifyModel param)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            var sql = @"";
            return NotDoneError();
        }
        #endregion

        #region 货品类别列表 GetErpSDMatKindList

        [MustLogin]
        [Description("货品类别列表")]
        [ReturnType(typeof(IList<OdsErpSDMatKind.NeceModel>))]
        public ApiResult GetErpSDMatKindList()
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                var cacheKey = "all-ErpSDMatKind-list" + UserInfo.UserCompanyId;
                var result = RedisCache.GetAndDecodeJson<IList<OdsErpSDMatKind.NeceModel>>(cacheKey);
                if (result == null)
                {
                    var dt = ReturnErpSDMatKindList();
                    result = ModelConvertHelper<OdsErpSDMatKind.NeceModel>.ConvertToModel(dt).ToList();
                    RedisCache.MinString(cacheKey, result.ToJson(), 30);
                }
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
        #endregion

        #region 货品款型列表 GetOdsErpSDMatModel

        [MustLogin]
        [Description("货品款型列表")]
        [ReturnType(typeof(IList<OdsErpSDMatModel.NeceModel>))]
        public ApiResult GetOdsErpSDMatModel()
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                var cacheKey = "all-OdsErpSDMatModel-list-" + UserInfo.UserCompanyId;
                var result = RedisCache.GetAndDecodeJson<IList<OdsErpSDMatModel.NeceModel>>(cacheKey);
                if (result == null)
                {
                    result = ReturnOdsErpSDMatModel();
                    RedisCache.MinString(cacheKey, result.ToJson(), 30);
                }
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
        #endregion

        #endregion


        #region (ok)返回DataTable ReturnDWSuppleAllMessageList

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="FHStockID"></param>
        /// <param name="CityID"></param>
        /// <param name="ShopLevelCode"></param>
        /// <param name="YearNo"></param>
        /// <param name="SeasonName"></param>
        /// <param name="KindName"></param>
        /// <param name="ModelName"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public DataTable ReturnDWSuppleAllMessageList(string FHStockID, string ProID, string CityID, string ShopKeys, string KindName, string YearNo, string SeasonName, string MaterialID,
             string ModelName, long? SuppleQty, string ShopLevelCode, string Project, string Gender, string ChildProject)
        {
            try
            {
                var sql = $@"select  sam.*,ds.ShopName,ds.ShopCode,ds.ShopID,ds.AreaID,ds.StockID,eba.AreaID,eba.AreaCode,eba.AreaName,eba.FullName  from ndw.nDW_SuppleAllMessage sam
                            inner join ndw.ndw_Shop ds on sam.CompanyID = ds.CompanyID and sam.ShopCode=ds.ShopCode
                            left join nods.nods_erp_Bas_Area eba on ds.CompanyID = eba.ExtrSource and ds.AreaID=eba.AreaID
                            where 1=1 and  sam.CompanyID = '{UserInfo.UserCompanyId}' and sam.FHStockID = '{FHStockID}' {(string.IsNullOrEmpty(ProID) ? "" : $@" and eba.ParentID = '{ProID}' ")} 
                            {(string.IsNullOrEmpty(CityID) ? "" : $@" and ds.AreaID = '{CityID}' ")} 
                            {(string.IsNullOrEmpty(ShopKeys) ? "" : $@" and (ds.ShopName like '%{ShopKeys}%' or ds.ShopCode like '%{ShopKeys}%') ")}
                            {(string.IsNullOrEmpty(KindName) ? "" : $@" and sam.KindName = '{KindName}' ")} 
                            {(string.IsNullOrEmpty(YearNo) ? "" : $@" and sam.YearNo = '{YearNo}' ")} 
                            {(string.IsNullOrEmpty(SeasonName) ? "" : $@" and sam.SeasonName = '{SeasonName}' ")} 
                            {(string.IsNullOrEmpty(Gender) ? "" : $@" and sam.SexName = '{Gender}' ")} 

                            {(string.IsNullOrEmpty(ChildProject) ? "" : $@" and sam.SubItem in ({StringTableHelper.ReturnSplitList(ChildProject)}) ")} 
                            {(string.IsNullOrEmpty(Project) ? "" : $@" and sam.ItemName in ({StringTableHelper.ReturnSplitList(Project)}) ")} 
                            {(string.IsNullOrEmpty(ModelName) ? "" : $@" and sam.ModelName in ({StringTableHelper.ReturnSplitList(ModelName)}) ")} 

                            {(string.IsNullOrEmpty(MaterialID) ? "" : $@" and sam.MaterialID = '{MaterialID}' ")}
                            {(SuppleQty == -1 ? "" : (SuppleQty == 1 ? @" and sam.UpdateSuppleQty > 0 " : @" and sam.SuppleQty > 0 "))} 
                            {(string.IsNullOrEmpty(ShopLevelCode) ? "" : $@" and ds.ShopLevelCode = '{ShopLevelCode}'")}
                            {($@" and sam.StockDate = '{DateTime.Now.AddDays(-1):yyyy-MM-dd}'")} 
                            order by sam.ShopCode,sam.MaterialID ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)补货总量 TotalDWSuppleAllMessageList

        [MustLogin]
        [Description("补货总量")]
        [ReturnType(typeof(IList<DWSuppleAllMessageModel.ListModel>))]
        public ApiResult TotalDWSuppleAllMessageList([ParamDescription("发货仓库")] string FHStockID)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                if (string.IsNullOrWhiteSpace(FHStockID)) return Error("发货仓库不能为空！");
                var sql = $@"select sum(UpdateSuppleQty) from ndw.ndw_SuppleAllMessage 
                          where StockDate='{DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd")}' AND  RobSuppleQty>0 AND  CompanyID = '{UserInfo.UserCompanyId}' AND  FHStockID = '{FHStockID}'";


                // Log.WriteLines(sql);
                var cmd = Helper.GetSqlStringCommand(sql);
                var value = Helper.ExecuteScalar(cmd);
                var total = Convert.IsDBNull(value) ? 0 : Convert.ToInt32(value);

                return Success(total);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
        #endregion

        #region (ok)返回DataTable ReturnDWAuxiliaryInformationList

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="fHStockID"></param>
        /// <param name="shopCode"></param>
        /// <param name="materialID"></param>
        /// <returns></returns>
        public DataTable ReturnDWAuxiliaryInformationList(string FHStockID, string ShopCode, string MaterialID)
        {
            try
            {
                string companyId = UserInfo.UserCompanyId;
                var sql = $@"select * from ndw.ndw_SuppleAllMessage sam
                left join (
                    select sum(CanUseQty) CanUseQty,SizeID,Stockdate,FHStockID,MaterialID 
                    from (
                        select case SizeID when '00000005' then '00000008'else SizeID end,Stockdate,FHStockID,MaterialID,CanUseQty
                        from ndw.nDW_FHStockCanUse 
                        where CompanyID = '{companyId}'
                    ) table1
                    group by SizeID,Stockdate,FHStockID,MaterialID
                ) fc 
                on sam.FHStockID=fc.FHStockID and sam.MaterialID=fc.MaterialID and sam.SizeID=fc.SizeID and sam.StockDate=fc.Stockdate
                where  sam.CompanyID = '{companyId}' and  sam.FHStockID ='{FHStockID}' and sam.ShopCode='{ShopCode}' and sam.MaterialID='{MaterialID}'
                and sam.StockDate='{DateTime.Now.AddDays(-1):yyyy-MM-dd}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)返回DataTable ReturnTotalInventory

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="FHStockID"></param>
        /// <param name="CityID"></param>
        /// <param name="ShopLevelCode"></param>
        /// <param name="YearNo"></param>
        /// <param name="SeasonName"></param>
        /// <param name="KindName"></param>
        /// <param name="ModelName"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public DataTable ReturnTotalInventory(string FHStockID, string MaterialID, string ShopCode, long? SuppleQty)
        {
            try
            {
                var sql = $@"
select sam.FHStockID,sam.ShopCode,sam.MaterialID,sam.SizeID,sam.SizeName,
sam.EndQty,sam.PathQty,sam.OrderPathQty,sam.UpdateSuppleQty,sam.AvgWeekSaleQty
from ndw.nDW_SuppleAllMessage sam
where 1=1 and sam.CompanyID = '{UserInfo.UserCompanyId}' and sam.FHStockID = '{FHStockID}' 
{($@" and sam.MaterialID in ({MaterialID}) ")}
{(SuppleQty == -1 ? "" : (SuppleQty == 1 ? @" and sam.UpdateSuppleQty <= 0 " : @" and sam.SuppleQty <= 0 "))} 
{($@" and sam.StockDate = '{DateTime.Now.AddDays(-1):yyyy-MM-dd}'")}
{($@"and sam.ShopCode in ({ShopCode})")}
order by MaterialID,sam.ShopCode";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)返回总条数 ReturnTotalDWSuppleAllMessage

        /// <summary>
        /// 返回总条数
        /// </summary>
        /// <param name="FHStockID"></param>
        /// <param name="CityID"></param>
        /// <param name="ShopLevelCode"></param>
        /// <param name="YearNo"></param>
        /// <param name="SeasonName"></param>
        /// <param name="KindName"></param>
        /// <param name="ModelName"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public int ReturnTotalDWSuppleAllMessage(string FHStockID, string ProID, string CityID, string ShopKeys, string KindName, string YearNo, string SeasonName, string MaterialID,
             string ModelName, long? SuppleQty, string ShopLevelCode)
        {
            try
            {
                var sql = $@"select count(1) from(
select count(1) from ndw.nDW_SuppleAllMessage sam
inner join ndw.ndw_Shop ds on sam.CompanyID = ds.CompanyID and sam.ShopCode=ds.ShopCode
left join nods.nods_erp_Bas_Area eba on eba.ExtrSource = ds.CompanyID and ds.AreaID=eba.AreaID
where 1=1 and sam.CompanyID = '{UserInfo.UserCompanyId}' and  sam.FHStockID = '{FHStockID}' {(string.IsNullOrEmpty(ProID) ? "" : $@" and eba.ParentID = '{ProID}' ")} {(string
                    .IsNullOrEmpty(CityID)
                    ? ""
                    : $@" and ds.AreaID = '{CityID}' ")} {(string
                    .IsNullOrEmpty(ShopKeys)
                    ? ""
                    : $@" and (ds.ShopName like '%{ShopKeys}%' or ds.ShopCode like '%{ShopKeys}%') ")} {(string
                    .IsNullOrEmpty(KindName)
                    ? ""
                    : $@" and sam.KindName = '{KindName}' ")} {(string.IsNullOrEmpty(YearNo)
                    ? ""
                    : $@" and sam.YearNo = '{YearNo}' ")} {(string.IsNullOrEmpty(SeasonName)
                    ? ""
                    : $@" and sam.SeasonName = '{SeasonName}' ")}{(string.IsNullOrEmpty(MaterialID)
                    ? ""
                    : $@" and sam.MaterialID = '{MaterialID}' ")} {(string.IsNullOrEmpty(ModelName)
                    ? ""
                    : $@" and sam.ModelName = '{ModelName}' ")} {(SuppleQty == -1
                    ? ""
                    : (SuppleQty == 1 ? @" and sam.UpdateSuppleQty > 0 " : @" and sam.SuppleQty > 0 "))} {(string
                    .IsNullOrEmpty(ShopLevelCode)
                    ? ""
                    : $@" and ds.ShopLevelCode = '{ShopLevelCode}'")}
{($@" and sam.StockDate = '{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}'")}
group by sam.FHStockID,sam.MaterialID,sam.ShopCode) table1 ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)修改智能补货结果列表 ModifyDWSuppleAllMessage

        [JsonType]
        [MustLogin]
        [Description("修改智能补货结果列表")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModifyDWSuppleAllMessage([ParamDescription("修改列表")]DWSuppleAllMessageModel.ModifyModel param)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var kc = ModelConvertHelper<DWFHStockCanUseModel.NeceModel>.ConvertToModel(ReturnDWFHStockCanUseList(param.list.First().FHStockID, param.list.First().MaterialID));
                    if (kc.Count <= 0) return Error($@"货号为{param.list.First().MaterialID}的货物库存不足!");
                    var cmdList = new List<CommandInfo>()
                    {
                        new CommandInfo()
                                {
                                    CommandText = $"UPDATE ndw.ndw_SuppleAllMessage SET isModify=true " +
                                                  $"where  CompanyID = '{UserInfo.UserCompanyId}' and  StockDate='{param.list.First().StockDate.Substring(0, 10)}' " +
                                                  $"and FHStockID='{param.list.First().FHStockID}' and MaterialID='{param.list.First().MaterialID}' ",
                                }
                    };
                    foreach (var item in param.list)
                    {
                        if (kc.Any(d => d.SizeID == item.SizeID))
                        {
                            if (kc.Where(d => d.SizeID == item.SizeID).Select(d => d.CanUseQty).First() < item.UpdateSuppleQty)
                            {
                                return Error($@"尺码为{item.SizeName}的商品库存不足!");
                            }
                            else
                            {
                                var cmd = new CommandInfo()
                                {
                                    CommandText = $"UPDATE ndw.ndw_SuppleAllMessage SET UpdateSuppleQty={item.UpdateSuppleQty} " +
                                                  $"where  CompanyID = '{UserInfo.UserCompanyId}' and  StockDate='{item.StockDate.Substring(0, 10)}' and FHStockID='{item.FHStockID}' " +
                                                  $"and ShopCode='{item.ShopCode}' and MaterialID='{item.MaterialID}' and SizeID='{item.SizeID}' and SizeTypeCode='{item.SizeTypeCode}'",
                                };
                                cmdList.Add(cmd);
                            }
                        }
                        else
                        {
                            return Error($@"{param.list.First().MaterialID}的货物中不存在尺码为{item.SizeName}的商品!");
                        }
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
        #endregion

        #region (ok)返回DataTable ReturnDWFHStockCanUseList

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="FHStockID"></param>
        /// <param name="MaterialID"></param>
        /// <returns></returns>
        public DataTable ReturnDWFHStockCanUseList(string FHStockID, string MaterialID)
        {
            try
            {
                var sql = $@"select * from 
(
    select sum(CanUseQty) CanUseQty,SizeID,Stockdate,FHStockID,MaterialID 
    from (select case SizeID when '00000005' then '00000008' else SizeID end,Stockdate,FHStockID,MaterialID,CanUseQty from ndw.nDW_FHStockCanUse where CompanyID = '{UserInfo.UserCompanyId}' ) table1 
	group by SizeID,Stockdate,FHStockID,MaterialID
) fc 
left join (	select sum(UpdateSuppleQty) as Taken,SizeID,SizeName 
                from ndw.nDW_SuppleAllMessage 
				where CompanyID = '{UserInfo.UserCompanyId}' and FHStockID='{FHStockID}' and MaterialID='{MaterialID}' and StockDate = '{DateTime.Now.AddDays(-1):yyyy-MM-dd}' 
				Group By SizeID,SizeName 
			) as table1 
on fc.SizeID=table1.SizeID 
where FHStockID='{FHStockID}' and MaterialID='{MaterialID}' and StockDate = '{DateTime.Now.AddDays(-1):yyyy-MM-dd}' and CanUseQty>0 
order by fc.SizeID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)下载调拨数据 ExportDWSuppleAllMessageList

        /// <summary>
        /// 下载调拨数据
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        public DataTable ExportDWSuppleAllMessageList(string Keys)
        {
            try
            {
                var sql = $@"
select sam.*,ebs.StockCode as StockCode,ebs.StockName,ebsfh.StockCode as FHStockCode,ebsfh.StockName as FHStockName 
from ndw.vw_nDW_SuppleAllMessage sam
inner join nods.nods_erp_Bas_Stock ebsfh on sam.ExtrSource = ebsfh.CompanyID and sam.FHStockID=ebsfh.StockID
inner join nods.nods_erp_Bas_Stock ebs on sam.ExtrSource = ebs.CompanyID and sam.StockID=ebs.StockID
inner join ndw.ndw_Shop ds on sam.ExtrSource = ds.CompanyID and sam.ShopCode=ds.ShopCode
left join nods.nods_erp_Bas_Area eba on ds.CompanyID = eba.ExtrSource and  ds.AreaID=eba.AreaID
where 1=1 and  sam.ExtrSource = '{UserInfo.UserExtrSource}' and  sam.StockDate='{DateTime.Now.AddDays(-1):yyyy-MM-dd}' {(!string.IsNullOrEmpty(Keys) ? Keys : "")}
order by StockDate desc,MaterialID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)返回DataTable ReturnErpSDMatKindList

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable ReturnErpSDMatKindList()
        {
            try
            {
                string sql = "SELECT * FROM nods.nods_erp_SD_Mat_Kind " +
                             $"where ExtrSource = '{UserInfo.UserExtrSource}' ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)返回DataTable ReturnOdsErpSDMatModel

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <returns></returns>
        public IList<OdsErpSDMatModel.NeceModel> ReturnOdsErpSDMatModel()
        {
            try
            {
                string sql = "select * from nods.nods_erp_SD_Mat_Model " +
                             $"where ExtrSource = '{UserInfo.UserExtrSource}'  ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<OdsErpSDMatModel.NeceModel>.ConvertToModel(dt).ToList();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)获取昨天补货补货最多的N间店铺 GetTopYesterdayDWSuppleAllMessage(ok)

        [MustLogin]
        [Description("获取昨天补货补货最多的N间店铺")]
        [ReturnType(typeof(IList<DWSuppleAllMessageModel.ChartModel>))]
        public ApiResult GetTopYesterdayDWSuppleAllMessage([ParamDescription("指定店铺间数")] int? top = 10)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                //只查昨天的
                var date = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
                var sql = $@"select sum(sm.updatesuppleQty) as SuppleQtySum ,shop.ShopName 
from ndw.ndw_SuppleAllMessage sm  
right join ndw.ndw_Shop as shop on sm.CompanyID = shop.CompanyID and  sm.shopcode = shop.shopcode
where sm.CompanyID = '{UserInfo.UserCompanyId}'
group by shop.ShopName,sm.stockdate 
having sm.stockdate='{date}' 
order by sum(sm.updatesuppleQty) desc 
limit {top} ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<DWSuppleAllMessageModel.ChartModel>.ConvertToModel(dt).OrderBy(d => d.SuppleQtySum);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
        #endregion

        #region (ok)导出SKC畅滞销状态 ExportStateSKC

        /// <summary>
        /// 导出SKC畅滞销状态
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        public DataTable ExportStateSKC(string Keys)
        {
            try
            {
                var sql = $@"SELECT distinct sam.StockDate, sam.ShopName, sam.MaterialID, sam.SKCCZPFlag
FROM ndw.ndw_SuppleAllMessage sam
inner join ndw.ndw_Shop ds on sam.CompanyID = ds.CompanyID and sam.ShopCode=ds.ShopCode
left join nods.nods_erp_Bas_Area eba on ds.CompanyID = eba.ExtrSource and ds.AreaID=eba.AreaID
where 1=1 and  sam.CompanyID = '{UserInfo.UserCompanyId}' and  sam.StockDate='{DateTime.Now.AddDays(-1):yyyy-MM-dd}'
{(!string.IsNullOrEmpty(Keys) ? Keys : "")}
order by sam.StockDate desc,sam.MaterialID, sam.ShopName";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)下载辅助数据 ExportSecondaryData

        /// <summary>
        /// 下载辅助数据
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        public DataTable ExportSecondaryData(string Keys)
        {
            try
            {
                string companyId = UserInfo.UserCompanyId;
                string date = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
                var sql = $@"SELECT t1.*,fc.CanUseQty,(case t1.MoveOutFlag when 1 then '是' else '' end) MoveOutFlagCn
FROM ndw.ndw_SuppleAllMessage t1
inner join 
( 
    SELECT distinct sam.FHStockID,sam.ShopCode,sam.MaterialID,sam.lifeTotalSaleQty,sam.lifeTotalMoveQty,sam.SizeID
    FROM ndw.ndw_SuppleAllMessage sam
    inner join ndw.ndw_Shop ds on sam.CompanyID = ds.CompanyID and sam.ShopCode=ds.ShopCode
    left join nods.nods_erp_Bas_Area eba on ds.CompanyID = eba.ExtrSource and ds.AreaID=eba.AreaID
    where 1=1 and  sam.CompanyID = '{companyId}' and  sam.StockDate='{date}' 
    {(!string.IsNullOrEmpty(Keys) ? Keys : "")}
) t2 on t1.FHStockID=t2.FHStockID and t1.ShopCode=t2.ShopCode and t1.MaterialID=t2.MaterialID and t1.SizeID = t2.SizeID
left join (select sum(CanUseQty) CanUseQty,SizeID,Stockdate,FHStockID,MaterialID 
from (
    select case SizeID when '00000005' then '00000008'else SizeID end,Stockdate,FHStockID,MaterialID,CanUseQty
    from ndw.nDW_FHStockCanUse
     where CompanyID = '{companyId}'
) table1
group by SizeID,Stockdate,FHStockID,MaterialID) fc on t1.FHStockID=fc.FHStockID and t1.MaterialID=fc.MaterialID and t1.SizeID=fc.SizeID and t1.StockDate=fc.Stockdate
where  t1.CompanyID = '{companyId}' and  t1.StockDate='{date}'
order by t1.ShopCode,t1.MaterialID ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 尺码内容
        [MustLogin]
        [Description("尺码内容")]
        [ReturnType(typeof(IList<OdsErpSDMatKind.NeceModel>))]
        public ApiResult GetSizeTextTypeList()
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                var cacheKey = "all-SizeTextType-list" + UserInfo.UserCompanyId;
                var result = RedisCache.GetAndDecodeJson<IList<DWFHStockCanUseModel.ListSizeModel>>(cacheKey);
                if (result == null)
                {
                    string sql = string.Format(@"select SizeTypeCode,SizeName from ndw.ndw_SizeTextType where CompanyID='{0}' order by SizeTypeCode,SizeID", UserInfo.UserExtrSource);
                    var cmd = Helper.GetSqlStringCommand(sql);
                    var dt = Helper.ExecuteDataTable(cmd);
                    result = ModelConvertHelper<DWFHStockCanUseModel.ListSizeModel>.ConvertToModel(dt).ToList();
                    RedisCache.MinString(cacheKey, result.ToJson(), 30);
                }
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }




        #endregion


        /// <summary>
        /// 获取下拉框数据
        /// </summary>
        /// <param name="Type"></param>
        /// <returns></returns>
        [MustLogin]
        [Description("获取下拉框数据")]
        public ApiResult GetSelectList(string Types)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            string cacheKey = string.Empty;
            //var result = RedisCache.GetAndDecodeJson<IList<nodsErpSdMatItem>>(cacheKey);
            try
            {
                switch (Types)
                {
                    //货品项目数据源 
                    case "Project":
                        cacheKey = "all-nodsErpSdMatItem-list-" + UserInfo.UserCompanyId;
                        var result1 = RedisCache.GetAndDecodeJson<IList<NodsErpSdMatItem>>(cacheKey);
                        if (result1 == null)
                        {
                            result1 = new GetTableData<NodsErpSdMatItem>().GetDataByTableName("nods.nods_erp_sd_mat_Item", "distinct ItemID,ItemName ",
                                $"AND ExtrSource='{UserInfo.UserExtrSource}'").ToList();
                            RedisCache.MinString(cacheKey, result1.ToJson(), 30);
                        }
                        return Success(result1);
                    //子项目数据源
                    case "ChildProject":
                        cacheKey = "all-nodsErpSdMatMaterial2-list-" + UserInfo.UserCompanyId;
                        var result2 = RedisCache.GetAndDecodeJson<IList<NodsErpSdMatMaterial2>>(cacheKey);
                        if (result2 == null)
                        {
                            result2 = new GetTableData<NodsErpSdMatMaterial2>().GetDataByTableName("nods.nods_erp_sd_mat_material2", "distinct SubItem ",
                                $"AND ExtrSource='{UserInfo.UserExtrSource}' AND companyID='{UserInfo.UserExtrSource}' AND isnull(SubItem,'')<>''").ToList();
                            RedisCache.MinString(cacheKey, result2.ToJson(), 30);
                        }
                        return Success(result2);
                    //性别数据源
                    case "Gender":
                        cacheKey = "all-nodsErpSdMatSex-list-" + UserInfo.UserCompanyId;
                        var result3 = RedisCache.GetAndDecodeJson<IList<NodsErpSdMatSex>>(cacheKey);
                        if (result3 == null)
                        {
                            result3 = new GetTableData<NodsErpSdMatSex>().GetDataByTableName("nods.nods_erp_sd_mat_Sex", "distinct SexID,SexCode,SexName ",
                                $"AND ExtrSource='{UserInfo.UserExtrSource}'").ToList();
                            RedisCache.MinString(cacheKey, result3.ToJson(), 30);
                        }
                        return Success(result3);
                    default:
                        return Error("系统错误！");
                }

            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }



    }
}
